Multilingual data sources, such as multilingual databases, generally store translated label values representing words in different languages, such as English, German or French. Multilingual data schemas have been developed to store, relate and access those translated label values depending on the language of interest. A “translated label value,” or “TLV,” refers generally to the data representing a specific translated word from one of many languages, whereby a common meaning usually relates a collection of translated label values over a number of different languages. Examples of common multilingual data structures are depicted in FIGS. 1A to 1D.
FIG. 1A shows a portion of common data structure 100 for relating multilingual data for generating reports. Data structure 100 contains “duplicate rows” where each of the duplicated rows relates a specific translated label to a specific concept, such as a product identifier (“ID”). Column 102 includes a common characteristic, such a product ID, column 104 indicates a language to which a translated label belongs, and column 106 contains the translated labels (or words), such as “car,” “voiture,” and “wagen,” all of which relate to a common meaning. As such, these translated labels of duplicate rows relate to a group of common product IDs, such as groups 108 and 110. To find a specific label for a query, specific values in both columns 102 and 104 are required to determine a product name (“Prod_Name”). FIG. 1B shows a portion of another common data structure 120 for relating multilingual data in a manner similar to that of FIG. 1A. Data structure 120, however, contains “duplicate columns” where each of the duplicated columns relates a specific translated label to a product ID as a specific concept. Specifically, column 122 includes product IDs as a common characteristic, and duplicated columns 124 indicate product names having translated labels expressed in different languages. As shown, columns 124a, 124b, and 124c include translated labels in English, French, and German, respectively, and rows 228 and 230 identify specific product IDs, such as P0010 and P0020, respectively.
FIG. 1C depicts a portion of another traditional multilingual data structure that includes at least two separate data structures 140a and 140b, each of which contains labels of one specific language. In this case, table 140a is described as an English table 146a for words used in, for example, the United States (“US”), whereas table 140b is a French table 146b for words used in, for example, France. Columns 142a and 142b include product IDs and columns 144a and 144b includes respective translated labels, but in different languages. FIG. 1D illustrates a portion of yet another conventional multilingual data structure where a sub-table 170 of translated values is dynamically produced from a base table 160 to improve performance of data access as compared to the data structures shown in FIGS. 1A to 1C. In particular, base table 160 includes product IDs and a specific untranslated value, such as a “price,” in columns 162 and 164, respectively. Given product ID P0010, sub-table 170 is generated to include columns 172, 174 and 176 to relate product IDs, language, and product names, respectively.
While the foregoing data structures and associated multilingual data schemas are functional, there is a common drawback in the implementation of these data structures when reports are generated in different locales, especially where the languages at those locales are also different. Labels, such as product names, are traditionally hard-coded into a query for matching translated labels in a multilingual data source against which a user input queries. For instance, consider that one desires to determine the sales revenue for a given product, such as a product having an English name of “plane.” To perform a query on the sales revenue for a product named plane, one traditionally has to design a filtered query so that only rows matching this product are retrieved from the data source (when implementing a “duplicate rows” schema). When building a filtered query, a user typically uses the product names that are in their own language, rather than either in a foreign language or as represented by some untranslatable attribute, such as a product identifier, that is not readily discernable to a user. Generally, untranslatable attributes are identifiers that are internal to a database and are usually understood only by those skilled in manipulating specialized database programming languages. To build a conventional filtered query for the English-named product plane, a skilled user typically develops a code snippet equivalent to WHERE PRODUCT.PROD_NAME=“plane” to query a multilingual database. The drawback to this approach is that in another locale (or language), the translated label in one language cannot match the user input in another language, and as such, a regenerated report at another locale can lead to improper query results. For example, if a query is coded to only search for French labels of table 100, as defined by a specific language field, LANG “FR,” then English words, such as “plane,” cannot be used to filter the query when it is rerun, unless the software processes conducting such a query are modified for use with the English language. As such modifications are typically manual in nature, there are additional costs and/or efforts required to implement the data structures of FIGS. 1A to 1D in multilingual database operations, such as in report generation.
In view of the foregoing, it would be highly desirable to provide an improved technique for generating reports from data maintained in multilingual data sources. In particular, it would be highly desirable to generate reports in a language at a locale different from that where the original query was formed, without manually modifying queries and/or software programs to do so.